package cn.pengpeng.sql;

/**
每天的活跃用户
         创建表 用来加载清洗好的数据          
create table ods_app_log(
      cid_sn            string,
      mobile_data_type  string,
      os_ver            string,
      mac               string,
      resolution        string,
      commit_time       string,
      sdk_ver           string,
      device_id_type    string,
      city              string,
      android_id        string,
      device_model      string,
      carrier           string,
      promotion_channel string,
      app_ver_name      string,
      imei              string,
      app_ver_code      string,
      pid               string,
      net_type          string,
      device_id         string,
      app_device_id     string,
      release_channel   string,
      country           string,
      time_zone         string,
      os_name           string,
      manufacture       string,
      commit_id         string,
      app_token         string,
      account           string,
      app_id            string,
      build_num         string,
      language          string,
      uid               string
)
partitioned by(day string)
row format delimited 
fields terminated by ",";

         加载数据         
load data local inpath "/root/data/20170101" into table ods_app_log partition(day="20170101");

         创建活跃用户的表         
create table etl_user_active_day(
   uid string,
   commit_time string,
   city string,
   release_channel string,
   app_ver_name string
)partitioned by (day string);

          计算数据到活跃用户表          
          20170101           
insert into table etl_user_active_day partition(day="20170101")
select uid,commit_time,city , release_channel,app_ver_name
from 
(select 
uid,commit_time, city , release_channel,app_ver_name,
row_number() over(partition by uid order by commit_time) as rn
from ods_app_log where day="20170101") tmp1
where tmp1.rn=1;

insert into table etl_user_active_day partition(day="20170102")
select uid,commit_time,city , release_channel,app_ver_name
from 
(select 
uid,commit_time, city , release_channel,app_ver_name,
row_number() over(partition by uid order by commit_time) as rn
from ods_app_log where day="20170102") tmp1
where tmp1.rn=1;

            分维度进行统计           
时间	城市	渠道	版本	活跃用户
当天	0		0		0 
当天	0		0		1
当天	0		1		0
当天	0		1		1
当天	1		0		0
当天	1		0		1
当天	1		1		0
当天	1		1		1

         创建维度统计表         
create table dim_user_active(
   city string,
   release_channel string,
   app_ver_name string,
   active_user_cnt int
)partitioned by (day string,flag string);

//多重插入   读一次数据，多次计算
from etl_user_active_day

insert into table dim_user_active partition(day="20170101",flag="000")
select "all","all","all",count(1)  where day="20170101"

insert into table dim_user_active partition(day="20170101",flag="001")
select "all","all",app_ver_name,count(1) where day="20170101"
group by app_ver_name

insert into table dim_user_active partition(day="20170101",flag="010")
select "all",release_channel,"all",count(1) where day="20170101"
group by release_channel

insert into table dim_user_active partition(day="20170101",flag="011")
select "all",release_channel,app_ver_name,count(1) where day="20170101"
group by release_channel,app_ver_name

insert into table dim_user_active partition(day="20170101",flag="100")
select city,"all","all",count(1) where day="20170101"
group by city

insert into table dim_user_active partition(day="20170101",flag="101")
select city,"all",app_ver_name,count(1) where day="20170101"
group by city,app_ver_name

insert into table dim_user_active partition(day="20170101",flag="110")
select city,release_channel,"all",count(1) where day="20170101"
group by city,release_channel

insert into table dim_user_active partition(day="20170101",flag="111")
select city,release_channel,app_ver_name,count(1) where day="20170101"
group by city,release_channel,app_ver_name;

**/

public class Rihuo {

}
